﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DataLayer
{
    public class dbListe
    {
        string Satici = System.Configuration.ConfigurationSettings.AppSettings["CariKodu"];
        static string SSatici = System.Configuration.ConfigurationSettings.AppSettings["CariKodu"];

        public DataTable CariListeKod(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = '%' + filtre.Replace('*', '%') + '%';
            return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     code AS [Müşteri Kodu], name AS [Müşteri Ünvanı],order_series as [Evrak Seri],city as [İL],district as [İlçe],taxoffice as [Vergi Daire],taxnumber as [Vergi No] FROM         CUSTOMERS  WHERE  (code not like '320%') and  (code LIKE @kod)", prm);
        }

        public string CariUnvani(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = filtre;
            return Factory.GetFactory.ExecuteScaler(CommandType.Text, "select isnull((SELECT  name + ';' + order_series + ';' + (case when logged_in =1 then 'var' else 'yok' end) + ';' + isnull(adress + ' ' + city + ' ' + district,'') + ';' + isnull(area_code,'') + ';' + isnull(seller_person,'') FROM         CUSTOMERS  WHERE  (code = @kod)),'0')", prm).ToString();
        }

        public DataTable CariListeAd(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = filtre.Replace('*', '%') + '%';
            return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     code AS [Müşteri Kodu], name AS [Müşteri Ünvanı],order_series as [Evrak Seri],city as [İL],district as [İlçe],taxoffice as [Vergi Daire],taxnumber as [Vergi No] FROM         CUSTOMERS  WHERE  (code not like '320%') and  (name LIKE @kod) and (logged_in=1) order by name", prm);
        }

        public DataTable CariListeAd2(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = filtre.Replace('*', '%') + '%';
            return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     code AS [Müşteri Kodu], name AS [Müşteri Ünvanı],order_series as [Evrak Seri],city as [İL],district as [İlçe],taxoffice as [Vergi Daire],taxnumber as [Vergi No] FROM         CUSTOMERS  WHERE  (code not like '320%') and  (name LIKE @kod)", prm);
        }

        public DataTable SupplierListeAd(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = '%' + filtre.Replace('*', '%') + '%';
            return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     code AS [Müşteri Kodu], name AS [Müşteri Ünvanı] FROM         CUSTOMERS  WHERE  (code not like '120%') and  (name LIKE @kod)", prm);
        }

        public DataTable CekilisListesi()
        {
            return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT CEKILISLER.cekilis_no AS No, CEKILISLER.tedarikci_kodu AS [Tedarikçi Kodu], CUSTOMERS.name AS [Tedarikçi Adı], CEKILISLER.cekilis_tarihi AS Tarih,CEKILISLER.cekilis_limit AS Limit, CEKILISLER.cekilis_aciklama AS Açıklama FROM CEKILISLER INNER JOIN CUSTOMERS ON CEKILISLER.tedarikci_kodu = CUSTOMERS.code");
        }

        public DataTable StokListeKod(string filtre, bool miktar)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            IDbDataParameter satici = Factory.GetFactory.GetParameter("@satici", DbType.String);
            prm.Value = '%' + filtre.Replace('*', '%') + '%';
            satici.Value = Satici;
            if (miktar)
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     MAX(barcode) AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim,cast(0 as decimal(16,2)) as Miktar FROM         PRODUCTS WHERE     (supplier = @satici) AND (code LIKE @kod) GROUP BY code, name, price, vat, dbo.DovizConvert(currency), ISNULL(discount1, 0), ISNULL(discount2, 0), ISNULL(discount3, 0), unit", prm, satici);
            else
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     MAX(barcode) AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim FROM         PRODUCTS WHERE     (supplier = @satici) AND (code LIKE @kod) GROUP BY code, name, price, vat, dbo.DovizConvert(currency), ISNULL(discount1, 0), ISNULL(discount2, 0), ISNULL(discount3, 0), unit", prm, satici);
        }

        public DataTable StokListeAd(string filtre, bool miktar)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            IDbDataParameter satici = Factory.GetFactory.GetParameter("@satici", DbType.String);
            prm.Value = '%' + filtre.Replace('*', '%') + '%';
            satici.Value = Satici;
            if (miktar)
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     MAX(barcode) AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim,cast(0 as decimal(16,2)) as Miktar FROM         PRODUCTS WHERE     (supplier = @satici) AND (name LIKE @kod)  GROUP BY code, name, price, vat, dbo.DovizConvert(currency), ISNULL(discount1, 0), ISNULL(discount2, 0), ISNULL(discount3, 0), unit", prm, satici);
            else
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     MAX(barcode) AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim FROM         PRODUCTS WHERE     (supplier = @satici) AND (name LIKE @kod) GROUP BY code, name, price, vat, dbo.DovizConvert(currency), ISNULL(discount1, 0), ISNULL(discount2, 0), ISNULL(discount3, 0), unit", prm, satici);
        }

        public DataTable StokListeBarkod(string filtre, bool miktar)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            IDbDataParameter satici = Factory.GetFactory.GetParameter("@satici", DbType.String);
            prm.Value = '%' + filtre.Replace('*', '%') + '%';
            satici.Value = Satici;
            if (miktar)
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     barcode AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim,cast(0 as decimal(16,2)) as Miktar FROM         PRODUCTS WHERE     (supplier = @satici) AND (barcode LIKE @kod)", prm, satici);
            else
                return Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     barcode AS Barkod, code AS [Ürün Kodu], name AS [Ürün Adı], price AS Fiyatı,vat as Kdv,dbo.DovizConvert(currency) as Döviz,isnull(discount1,0) as İsk1,isnull(discount2,0) as İsk2,isnull(discount3,0) as İsk3,unit as Birim FROM         PRODUCTS WHERE     (supplier = @satici) AND (barcode LIKE @kod)", prm, satici);
        }



        public static string[] UrunBul(string filtre)
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            IDbDataParameter satici = Factory.GetFactory.GetParameter("@satici", DbType.String);
            prm.Value = filtre;
            satici.Value = SSatici;
            object obj = Factory.GetFactory.ExecuteScaler(CommandType.Text, "SELECT     (barcode + ';' + isnull(code,'') + ';' + name + ';' + replace(cast(price as varchar(20)),'.',',') + ';' + cast(vat as varchar) + ';' + dbo.DovizConvert(currency) + ';' + cast(isnull(discount1,0) as varchar) + ';' + cast(isnull(discount2,0) as varchar) + ';' + cast(isnull(discount3,0) as varchar) + ';' + unit ) as bilgi FROM         PRODUCTS  WITH (NOLOCK)  WHERE     (supplier = @satici) AND (barcode=@kod or code=@kod) ", prm, satici);
            if (obj != null)
                return obj.ToString().Split(';');
            else
                return new string[] { "YOK" };
        }

        public static string[] CariSeri(string cariKod)
        {
            List<string> sonuc = new List<string>();
            string seri = "";

            IDataReader rd = Factory.GetFactory.ExecuteReader(CommandType.Text, string.Format("exec sp_executesql N'SELECT     name, order_series  FROM         CUSTOMERS  WITH (NOLOCK)   WHERE     (code = ''{0}'')'", cariKod));
            while (rd.Read())
            {
                seri = rd.GetString(1);
                sonuc.Add(seri);
                sonuc.Add(rd.GetString(0));
            }
            rd.Close();

            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            IDbDataParameter prm2 = Factory.GetFactory.GetParameter("@sira", DbType.Int32);
            prm.Value = seri;
            object obj = Factory.GetFactory.ExecuteScaler(CommandType.Text, "SELECT     isnull(MAX(order_no),0) FROM ORDER_DETAILS  WITH (NOLOCK)  WHERE (order_series = @kod) ", prm);
            if (!string.IsNullOrEmpty(obj.ToString()))
                sonuc.Add((Convert.ToInt32(obj) + 1).ToString());

            prm2.Value = Convert.ToInt32(obj) + 1;
            Factory.GetFactory.ExecuteScaler(CommandType.Text, "insert into ORDER_DETAILS (order_series,order_no) values (@kod,@sira) ", prm, prm2);


            return sonuc.ToArray();
        }


        public static List<string> CariMessages()
        {
            IDbDataParameter prm = Factory.GetFactory.GetParameter("@kod", DbType.String);
            prm.Value = SSatici;
            List<string> mesajlar = new List<string>();
            DataTable dt = Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     message  FROM         MESSAGES  WITH (NOLOCK)   WHERE     (supplier =@kod) order by rec_id desc", prm);
            if (dt.Rows.Count > 0)
                for (int i = 0; i < dt.Rows.Count; i++)
                    mesajlar.Add(dt.Rows[i][0].ToString());

            return mesajlar;
        }

        public DataTable EvrakListesi()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT     t1.order_series AS [Evrak Seri], t1.order_no AS [Evrak Sıra], t2.code AS [Cari Kodu], t2.name AS [Cari Ünvanı],CAST(SUM(t1.total) AS decimal(16, 2)) AS [Sipariş Tutarı],t1.printed as [Yazdırıldı],t2.area_code as [Bölge Kodu],t1.supplier as [Tedarikçi Kodu],(select name from CUSTOMERS where code=t1.supplier) as [Tedarikçi Adı] ");
            sb.Append(" FROM         ORDER_DETAILS t1 WITH (NOLOCK) INNER JOIN CUSTOMERS as t2 ON t1.customer_code = t2.code ");
            sb.Append(" GROUP BY t1.order_series, t1.order_no, t2.code, t2.name,t1.printed,t2.area_code,t1.supplier ");
            return Factory.GetFactory.ExecuteDataTable(sb.ToString());
        }

        public DataTable EvrakListesi(string seri)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT     t1.order_series AS [Evrak Seri], t1.order_no AS [Evrak Sıra], t2.code AS [Cari Kodu], t2.name AS [Cari Ünvanı],CAST(SUM(t1.total) AS decimal(16, 2)) AS [Sipariş Tutarı],t2.city as [İL],t2.district as [İlçe],t2.taxoffice as [Vergi Daire],t2.taxnumber as [Vergi No] ");
            sb.Append(" FROM         ORDER_DETAILS t1 WITH (NOLOCK) INNER JOIN CUSTOMERS as t2 ON t1.customer_code = t2.code where  t1.order_series like '{0}%' and t1.supplier ='{1}'  ");
            sb.Append(" GROUP BY t1.order_series, t1.order_no, t2.code, t2.name,t2.city,t2.district,t2.taxoffice,t2.taxnumber ");
            return Factory.GetFactory.ExecuteDataTable(string.Format(sb.ToString(), seri, Satici));
        }
    }
}
